﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_WA_GetDutyTime]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_WA_GetDutyTime];
GO
CREATE PROCEDURE [dbo].[sproc_WA_GetDutyTime] --得到上下班时间
    @staffid int,--人员id
    @ondutytime datetime output,--返回上班时间
    @offdutytime datetime output --返回下班时间
AS
/*
 * Get the expected duty time.
 */
BEGIN
SET NOCOUNT ON

--得到查询顺序即获取优先级信息
declare @id int;
declare @orderid int;
declare @ordername nvarchar(50);

set @orderid = 0;
set @id = @staffid;

while ( 1 = 1 )
begin
    select top 1 @orderid = id, @ordername = [name] from dbo.UDS_WorkAttendence_SettingSearchOrder where id > @orderid;
    if ( @@rowcount = 0 )
        return 1;

    if @ordername = N'Staff' 
    begin
        set @id = @staffid;
    end
    else 
    begin
        if( @ordername = N'Position')
        begin
            --根据staffid得到对应Positionid
            exec dbo.sproc_GetPositionFromStaff @staffid, @Positionid = @id output;
        end
        else
        begin
            if(@ordername = N'Company')
            begin
                set @id = 0;
            end
        end
    end

    --如果表里没有数据说明没有设置则检查上级,有就返回
    if(exists (select 1 from dbo.UDS_WorkAttendence_DutyTime where IDType = @ordername and ID = @id))
    begin
        select @ondutytime = ondutytime, @offdutytime = offdutytime from dbo.UDS_WorkAttendence_DutyTime  where IDType = @ordername and ID = @id;
        return 0;
    end
end
return 1;

END
GO